一、第九堂回顧：產生隨機的排班資料

(一) 情境練習題目：

使用Insert Into SELECT 產生實際做家事的資料

(二) 參考作法：

MySQL：

INSERT INTO CleanRecord
SELECT UUID() AS CleanRecordId,
	   CleanScheduleId,
	   FamilyId,
	   DATE_ADD(CAST(CONCAT(CAST(CleanDateTime AS DATE), ' 18:00:00') AS DATETIME) , INTERVAL FLOOR(RAND() * 30) MINUTE) AS StartTime,
	   DATE_ADD(CAST(CONCAT(CAST(CleanDateTime AS DATE), ' 19:00:00') AS DATETIME) , INTERVAL FLOOR(RAND() * 60) MINUTE) AS EndTime
FROM CleanSchedule

SQL Server

INSERT INTO CleanRecord
SELECT NEWID() AS CleanRecordId,
	   CleanScheduleId,
	   FamilyID,
	   DATEADD(MINUTE, FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 60), CAST(CONCAT(CAST(CleanDateTime AS DATE), ' ', '18:00') AS DATETIME) ) AS StartTime,
	   DATEADD(MINUTE, FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 90), CAST(CONCAT(CAST(CleanDateTime AS DATE), ' ', '19:00') AS DATETIME) ) AS EndTime
FROM CleanSchedule

(三) 講解

1. 每一筆紀錄的流水號：UUID()
2. 隨機產生打掃開始時間(StartTime)、打掃結束時間 (EndTime)，並確保結束時間>開始時間

MySQL：

-- 打掃開始時間
DATE_ADD(CAST(CONCAT(CAST(CleanDateTime AS DATE), ' 18:00:00') AS DATETIME) , INTERVAL FLOOR(RAND() * 30) MINUTE) AS StartTime,

-- 打掃結束時間
DATE_ADD(CAST(CONCAT(CAST(CleanDateTime AS DATE), ' 19:00:00') AS DATETIME) , INTERVAL FLOOR(RAND() * 60) MINUTE) AS EndTime

SQL Server：

-- 打掃開始時間
DATEADD(MINUTE, FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 60), CAST(CONCAT(CAST(CleanDateTime AS DATE), ' ', '18:00') AS DATETIME) ) AS StartTime,

-- 打掃結束時間
DATEADD(MINUTE, FLOOR(RAND(CAST(NEWID() AS VARBINARY)) * 90), CAST(CONCAT(CAST(CleanDateTime AS DATE), ' ', '19:00') AS DATETIME) ) AS EndTime
FROM CleanSchedule

二、第十堂：使用Update修改休息的成員排班

(一) 需求

第九堂的時候，我們學會如何產生亂數的排班表
然而，負責家事項目是4、5的人，其實是不用打掃的
所以我們要使用UPDATE SQL 將家事項目不存在於家事項目表(CleanItemList)的排班，家事項目欄位設為NULL

(二) 實作

UPDATE CleanSchedule
SET CleanSchedule.CleanItem = ''
WHERE CleanScheduleId IN (
                            SELECT CleanScheduleId
                            FROM (SELECT * FROM CleanSchedule) AS SubQueryCleanSchedule
                            LEFT JOIN CleanItemList
                            ON SubQueryCleanSchedule.CleanItem = CleanItemList.CleanItem
                            WHERE CleanItemList.CleanItem IS NULL
						 )

三、講解

第一個直接大家會想到WHERE 或 JOIN過濾不存在於家事項目清單的排班資料

使用後者的人
如果UPDATE的SELECT子查詢FROM的表格，跟UPDATE使用相同的表格
就會發生錯誤

UPDATE CleanSchedule
SET CleanSchedule.CleanItem = ''
WHERE CleanScheduleId IN (
                            SELECT CleanScheduleId
                            FROM CleanSchedule AS SubQueryCleanSchedule --跟UPDATE 使用相同的表格
                            LEFT JOIN CleanItemList
                            ON SubQueryCleanSchedule.CleanItem = CleanItemList.CleanItem　
                            WHERE CleanItemList.CleanItem IS NULL
                         )

因此FROM 改以子查詢解決

UPDATE CleanSchedule
SET CleanSchedule.CleanItem = ''
WHERE CleanScheduleId IN (
                            SELECT CleanScheduleId
                            FROM (SELECT * FROM CleanSchedule) AS SubQueryCleanSchedule --使用子查詢而非直接指定表格名稱
                            LEFT JOIN CleanItemList
                            ON SubQueryCleanSchedule.CleanItem = CleanItemList.CleanItem
                            WHERE CleanItemList.CleanItem IS NULL
						 )

四、認識UPDATE SET 語法

UPDATE 要修改的表格
SET 要修改的欄位1 = value1, 要修改的欄位2 = value2, ...
WHERE 過濾要修改的資料; 

五、相關情境練習

將上一堂作業產生的「實際做家事」資料，家事項目不存在於家事項目清單的做家事資料，打掃開始時間(Start Time)與打掃結束 (End Time)時間設為NULL